package com.example.skycode7.dao;

import com.example.skycode7.BEAN.NewsBean;
import com.example.skycode7.BEAN.NoticeBean;
import com.example.skycode7.utill.DBConnection;
import com.example.skycode7.utill.Time;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@@ -19,13 +18,13 @@ public class NoticeDao {
     */
    public int add(NoticeBean news){
        Connection conn = db.getConn();//conn代表mysql的连接
        String sql = "insert into news values(null,?,?,?,?)";//使用jdbc预处理对象添加
        String sql = "insert into notice values(null,?,?,?,?)";//使用jdbc预处理对象添加
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            //将新闻数据对象中数据依次绑定到sql命令中
            ps.setString(1,news.getNoticeTitle());
            ps.setString(2,news.getNoticeContent());
            ps.setString(3,news.getNoticeTime());
            ps.setString(3, Time.grtDateTime());
            ps.setString(4,news.getAdminName());
            return ps.executeUpdate();//执行sql命令 完成数据插入
        } catch (SQLException e) {
@@ -59,13 +58,13 @@ public class NoticeDao {
     */
    public int update(NoticeBean news){
        Connection conn = db.getConn();//conn代表mysql的连接
        String sql = "update news set NoticeTitle=?,NoticeContent=?,NoticeTime=?,AdminName=? where NoticeID=?";//使用jdbc预处理对象添加
        String sql = "update notice set NoticeTitle=?,NoticeContent=?,NoticeTime=?,AdminName=? where NoticeID=?";//使用jdbc预处理对象添加
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            //将新闻数据对象中数据依次绑定到sql命令中
            ps.setString(1,news.getNoticeTitle());
            ps.setString(2,news.getNoticeContent());
            ps.setString(3,news.getNoticeTime());
            ps.setString(3,Time.grtDateTime());
            ps.setString(4,news.getAdminName());
            ps.setInt(5,news.getNoticeId());
            return ps.executeUpdate();//执行sql命令 完成数据插入
@@ -82,7 +81,7 @@ public class NoticeDao {
     */
    public List<NoticeBean> getlist(int page){
        Connection conn = db.getConn();
        String sql = "select * from news limit ?,?";//第一个开始的行数 第二个是查询现实的条数
        String sql = "select * from notice limit ?,?";//第一个开始的行数 第二个是查询现实的条数
        List<NoticeBean> list = new ArrayList<>();
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
@@ -104,4 +103,40 @@ public class NoticeDao {
        }
        return list;//异常情况返回空集合
    }
}
    public int count() {
        Connection conn = db.getConn();
        String sql = "select count(*) from news";
        try {
            Statement statement = conn.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return 0;
    }

    public NoticeBean getOne(int id) {
        Connection conn = db.getConn();
        String sql = "select * from news where NewsID = ?";
        NoticeBean notice = new NoticeBean();
        try {
            PreparedStatement pmst = conn.prepareStatement(sql);
            pmst.setInt(1,id);
            ResultSet rs = pmst.executeQuery();
            if (rs.next()){
                notice.setNoticeId(rs.getInt("NoticeId"));
                notice.setNoticeTitle(rs.getString("NoticeTitle"));
                notice.setNoticeContent(rs.getString("NoticeContent"));
                notice.setNoticeTime(rs.getString("NoticeTime"));
                notice.setAdminName(rs.getString("AdminName"));
            }
        } catch(SQLException e){
            throw new RuntimeException(e);
        }
        return notice;
    }
    }
